package com.jwsoft.manager.core.integration.impl;

import cn.hutool.core.bean.BeanUtil;
import cn.hutool.core.convert.Convert;
import cn.hutool.core.date.DatePattern;
import cn.hutool.core.date.DateUtil;
import cn.hutool.core.io.FileUtil;
import cn.hutool.core.lang.Validator;
import cn.hutool.core.util.IdUtil;
import cn.hutool.core.util.IdcardUtil;
import cn.hutool.extra.servlet.ServletUtil;
import cn.hutool.poi.excel.ExcelWriter;
import com.alibaba.fastjson.JSON;
import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.bifang.engine.assistant.core.annotation.ApiService;
import com.bifang.engine.assistant.core.annotation.OpApi;
import com.bifang.engine.assistant.core.annotation.SaveOplog;
import com.bifang.engine.assistant.core.enums.AuTypeEnum;
import com.bifang.engine.assistant.core.enums.BoolEnum;
import com.bifang.engine.assistant.core.enums.FuncTypeEnum;
import com.bifang.engine.assistant.core.exception.AppException;
import com.bifang.engine.assistant.core.utils.SessionUtil;
import com.bifang.engine.cache.redis.RedisHelper;
import com.bifang.engine.data.core.util.PagerUtil;
import com.bifang.module.base.common.vo.dict.DictQueryVO;
import com.bifang.module.base.common.vo.dict.SelectVO;
import com.bifang.module.base.common.vo.excel.ExcelModelVO;
import com.bifang.module.base.common.vo.excel.ExcelParamVO;
import com.bifang.module.base.common.vo.excel.ExcelVO;
import com.bifang.module.base.core.helper.DictHelper;
import com.bifang.module.base.core.helper.ExcelCommonHelper;
import com.bifang.module.base.core.integration.DictIntegration;
import com.bifang.module.base.core.integration.ExcelIntegration;
import com.bifang.module.system.core.dao.model.SysApplication;
import com.bifang.module.system.core.dao.service.ExcelModelService;
import com.bifang.module.system.core.dao.service.ExcelParamService;
import com.bifang.module.system.core.dao.service.SysApplicationService;
import com.bifang.module.system.core.dao.service.SysDictService;
import com.github.pagehelper.PageHelper;
import com.github.pagehelper.PageInfo;
import com.jwsoft.manager.common.constant.EduCache;
import com.jwsoft.manager.common.enums.CardTypeEnum;
import com.jwsoft.manager.common.enums.JhSyncImportEnum;
import com.jwsoft.manager.common.enums.JhSyncTypeEnum;
import com.jwsoft.manager.common.vo.eduSyncJhApply.EduSyncJhApplyKeyVO;
import com.jwsoft.manager.common.vo.eduSyncJhApply.EduSyncJhApplyQueryVO;
import com.jwsoft.manager.common.vo.eduSyncJhApply.EduSyncJhApplyVO;
import com.jwsoft.manager.common.vo.eduSyncJhDelete.EduSyncJhDeleteQueryVO;
import com.jwsoft.manager.core.dao.model.EduSyncJhApply;
import com.jwsoft.manager.core.dao.model.EduSyncJhApplyError;
import com.jwsoft.manager.core.dao.model.EduSyncJhApprove;
import com.jwsoft.manager.core.dao.model.EduSyncJhEnroll;
import com.jwsoft.manager.core.dao.service.*;
import com.jwsoft.manager.core.integration.EduSyncJhApplyIntegration;
import com.jwsoft.manager.core.integration.EduSyncJhImportBatchIntegration;
import com.jwsoft.manager.core.util.EduDesensitizeUtil;
import com.jwsoft.manager.core.util.EduHelper;
import com.jwsoft.manager.core.util.ExcelUtils;
import org.apache.commons.lang3.StringUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.util.CollectionUtils;
import org.springframework.util.ObjectUtils;
import org.springframework.web.multipart.MultipartFile;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.BufferedInputStream;
import java.io.File;
import java.io.IOException;
import java.io.InputStream;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.time.LocalDateTime;
import java.time.format.DateTimeFormatter;
import java.time.format.DateTimeParseException;
import java.util.*;
import java.util.stream.Collectors;

/**
 * 金华市报名专库同步情况表业务实现类
 *
 * @author zhiyuan
 * @since 2024-04-08
 */
@ApiService(funcCode = "syncJhApply", title = "金华市报名专库同步情况表")
public class EduSyncJhApplyIntegrationImpl implements EduSyncJhApplyIntegration {

    @Autowired
    public EduSyncJhApplyService eduSyncJhApplyService;

    @Autowired
    private ExcelCommonHelper excelCommonHelper;

    @Autowired
    private RedisHelper redisHelper;

    @Autowired
    private ExcelIntegration excelIntegration;

    @Value("${spring.servlet.multipart.location}")
    private String tempPath;

    @Autowired
    public SysDictService sysDictService;

    @Autowired
    public ExcelParamService excelParamService;

    @Autowired
    public ExcelModelService excelModelService;

    @Autowired
    private DictIntegration dictIntegration;

    @Autowired
    private EduHelper eduHelper;

    @Autowired
    ExcelUtils excelUtils;

    @Autowired
    DictHelper dictHelper;

    @Autowired
    private EduSyncJhEnrollService enrollService;
    @Autowired
    private EduSyncJhApproveService approveService;

    @Autowired
    private EduSyncJhApplyErrorService applyErrorService;

    @Autowired
    private EduSyncJhImportBatchService importBatchService;

    @Autowired
    private EduSyncJhImportBatchIntegration importBatchIntegration;

    @Autowired
    private SysApplicationService applicationService;

    @Autowired
    private EduSyncJhDeleteService syncJhDeleteService;

    @Override
    @OpApi(funcCode = "syncJhApply0001", title = "金华市报名专库同步情况表分页查询", funcType = FuncTypeEnum.query, checkPrivilege = BoolEnum.FALSE)
    public PageInfo<EduSyncJhApplyVO> getList(EduSyncJhApplyQueryVO vo) {
        // 开启分页
        if (vo.getPageNum() == null || vo.getPageSize() == null) {
            //如果不传入分页参数，默认最大取1000条
            vo.setPageNum(1);
            vo.setPageSize(1000);
        }
        Boolean desensitizeFlag = vo.getDesensitizeFlag();
        if (desensitizeFlag == null){
            desensitizeFlag = true;
        }
        String loginAreaCode = SessionUtil.getAreaCode();
        if (StringUtils.isBlank(loginAreaCode)) {
            throw new AppException("登录的账号未配置行政区划，请联系管理员进行处理！");
        }
        QueryWrapper<EduSyncJhApply> queryWrapper = new QueryWrapper<>();
        String areaCode = eduHelper.thisAreaCode();
        //如果登录账号的行政区划和系统配置的行政区划/金华市 不一致，就比对行政区划
        if (!loginAreaCode.equals(areaCode)) {
            //比对行政区划
            queryWrapper.lambda().eq(EduSyncJhApply::getApplyCountyCode, loginAreaCode);
        }
        queryWrapper.lambda()
                .eq(!ObjectUtils.isEmpty(vo.getYear()),EduSyncJhApply::getYear, vo.getYear())
                .eq(StringUtils.isNotBlank(vo.getIdcard()), EduSyncJhApply::getIdcard, vo.getIdcard())
                .like(StringUtils.isNotBlank(vo.getName()), EduSyncJhApply::getName, vo.getName())
                .eq(StringUtils.isNotBlank(vo.getCardType()), EduSyncJhApply::getCardType, vo.getCardType())
                .eq(StringUtils.isNotBlank(vo.getSchoolCategory()), EduSyncJhApply::getSchoolCategory, vo.getSchoolCategory())
                .eq(StringUtils.isNotBlank(vo.getApplyCountyCode()), EduSyncJhApply::getApplyCountyCode, vo.getApplyCountyCode())
                .eq(StringUtils.isNotBlank(vo.getSex()), EduSyncJhApply::getSex, vo.getSex())
                .orderByDesc(EduSyncJhApply::getUpdateTime, EduSyncJhApply::getCreateTime, EduSyncJhApply::getName);
        PageHelper.startPage(vo.getPageNum(), vo.getPageSize());
        List<EduSyncJhApply> list = eduSyncJhApplyService.list(queryWrapper);
        if (CollectionUtils.isEmpty(list)) {
            // 判断是否为空，为空则返回空参数
            return PagerUtil.createEmptyPagerInfo(vo.getPageNum(), vo.getPageSize());
        }
        // 不为空则获取分页结果
        PageInfo<EduSyncJhApply> pageInfo = new PageInfo<>(list);
        // 返回参数需要通过common层的vo实体类，用该方法可以批量转换
        List<EduSyncJhApplyVO> resultList = Convert.toList(EduSyncJhApplyVO.class, pageInfo.getList());
        // 转义数据字典值
        dictHelper.valueToName(resultList, Arrays.asList(JhSyncTypeEnum.values()));
        if (desensitizeFlag) {
            for (EduSyncJhApplyVO applyVO : resultList) {
                applyVO.setName(EduDesensitizeUtil.desensitizeFullName(applyVO.getName()));
                applyVO.setIdcard(EduDesensitizeUtil.desensitizeIdcard(applyVO.getIdcard()));
            }
        }
        return PagerUtil.parsePagerVo(resultList, pageInfo);
    }

    @Override
    @OpApi(funcCode = "syncJhApply0002", title = "金华市报名专库同步情况表根据主键查询详情", funcType = FuncTypeEnum.query, checkPrivilege = BoolEnum.FALSE)
    public EduSyncJhApplyVO getById(EduSyncJhApplyKeyVO vo) {
        EduSyncJhApply entity = eduSyncJhApplyService.getById(vo.getJhApplyId());
        if (ObjectUtils.isEmpty(entity)) {
            throw new AppException("查询不到详细信息!  ");
        }
        EduSyncJhApplyVO result = Convert.convert(EduSyncJhApplyVO.class, entity);
        return result;
    }

    /**
     * 保存前统一校验
     */
    @Override
    @OpApi(funcCode = "syncJhApply0003", title = "金华市报名专库同步情况表保存前校验", funcType = FuncTypeEnum.other, checkPrivilege = BoolEnum.FALSE)
    public void checkSave(EduSyncJhApplyVO vo) {
        //主键为空为新增校验
        //主键不为空为修改校验
        if (vo.getYear() == null) {
            vo.setYear(eduHelper.thisYear());
        }
        if (StringUtils.isBlank(vo.getSchoolCategory())) {
            throw new AppException("学段不能为空！");
        }
        if (StringUtils.isBlank(vo.getCardType())) {
            throw new AppException("证件类型不能为空！");
        }
        if (StringUtils.isBlank(vo.getName())) {
            throw new AppException("姓名不能为空！");
        }
        if (StringUtils.isBlank(vo.getIdcard())) {
            throw new AppException("证件号码不能为空！");
        }
        if (StringUtils.isBlank(vo.getApplyCountyCode())) {
            throw new AppException("报名所在区县码不能为空！");
        }
        if (vo.getApplyTime() == null) {
            throw new AppException("报名时间戳不能为空！");
        }
        if (vo.getCardType().equalsIgnoreCase(CardTypeEnum.idcard.getType())) {
            if (!IdcardUtil.isValidCard(vo.getIdcard())) {
                throw new AppException("身份证格式有误！");
            }
            int gender = IdcardUtil.getGenderByIdCard(vo.getIdcard());
            if (gender == 1) {
                // 男
                vo.setSex("0");
            } else {
                // 女
                vo.setSex("1");
            }
        } else {
            if (StringUtils.isEmpty(vo.getSex())) {
                throw new AppException("性别不能为空！");
            }
            //校验性别是否符合要求
            if (!vo.getSex().equalsIgnoreCase("0")
                    && !vo.getSex().equalsIgnoreCase("1")) {
                throw new AppException("性别只能为数字0或1，0代表男，1代表女");
            }
        }
        boolean flag = false;
        if (StringUtils.isNotBlank(vo.getMasterGuardianName())) {
            if (StringUtils.isBlank(vo.getMasterGuardianNumber())) {
                throw new AppException("监护人1证件号码不能为空");
            }
            if (StringUtils.isBlank(vo.getMasterGuardianRelation())) {
                throw new AppException("监护人1与学生关系不能为空");
            }
            //校验关系格式
            if (!vo.getMasterGuardianRelation().equalsIgnoreCase("1")
                    && !vo.getMasterGuardianRelation().equalsIgnoreCase("2")
                    && !vo.getMasterGuardianRelation().equalsIgnoreCase("3")
                    && !vo.getMasterGuardianRelation().equalsIgnoreCase("4")) {
                throw new AppException("监护人1与学生关系只能为数字1或2或3或4，1代表父亲，2代表母亲，3代表祖父母或外祖父母，4代表其它");
            }
            flag = true;
        }

        if (StringUtils.isNotBlank(vo.getSlaveGuardianName())) {
            if (StringUtils.isBlank(vo.getSlaveGuardianNumber())) {
                throw new AppException("监护人2证件号码不能为空");
            }
            if (StringUtils.isBlank(vo.getSlaveGuardianRelation())) {
                throw new AppException("监护人2与学生关系不能为空");
            }
            //校验关系格式
            if (!vo.getSlaveGuardianRelation().equalsIgnoreCase("1")
                    && !vo.getSlaveGuardianRelation().equalsIgnoreCase("2")
                    && !vo.getSlaveGuardianRelation().equalsIgnoreCase("3")
                    && !vo.getSlaveGuardianRelation().equalsIgnoreCase("4")) {
                throw new AppException("监护人2与学生关系只能为数字1或2或3或4，1代表父亲，2代表母亲，3代表祖父母或外祖父母，4代表其它");
            }
            flag = true;
        }
        if (!flag) {
            throw new AppException("监护人至少填写一个");
        }

        //校验学段格式是否符合要求
        if ( !vo.getSchoolCategory().equalsIgnoreCase("2")
                && !vo.getSchoolCategory().equalsIgnoreCase("3")) {
            throw new AppException("学段只能为数字2或3，2代表小学，3代表初中");
        }
        //校验证件类型是否符合要求
        if (!vo.getCardType().equalsIgnoreCase("01")
                && !vo.getCardType().equalsIgnoreCase("02")
                && !vo.getCardType().equalsIgnoreCase("03")
                && !vo.getCardType().equalsIgnoreCase("04")
                && !vo.getCardType().equalsIgnoreCase("05")
                && !vo.getCardType().equalsIgnoreCase("06")
                && !vo.getCardType().equalsIgnoreCase("99")) {
            throw new AppException("证件类型只能为数字01或02或03或04或05或06或99，01代表身份证，02代表护照，03代表台胞证，04代表台湾居民来往大陆通行证，05代表港澳居民往来内地通行证，06代表外国人永久居留身份证，99代表其他证件");
        }

        //校验户口类型是否符合要求
        if (StringUtils.isNotBlank(vo.getHouseholdType())
                && !vo.getHouseholdType().equalsIgnoreCase("1")
                && !vo.getHouseholdType().equalsIgnoreCase("2")
                && !vo.getHouseholdType().equalsIgnoreCase("3")
                && !vo.getHouseholdType().equalsIgnoreCase("4")) {
            throw new AppException("户口类型只能为数字1或2或3或4，1代表居民，2代表农业，3代表渔民，4代表其他");
        }

        //校验监护人1与学生关系是否符合要求
        if (StringUtils.isNotBlank(vo.getMasterGuardianRelation())
                && !vo.getMasterGuardianRelation().equalsIgnoreCase("1")
                && !vo.getMasterGuardianRelation().equalsIgnoreCase("2")
                && !vo.getMasterGuardianRelation().equalsIgnoreCase("3")
                && !vo.getMasterGuardianRelation().equalsIgnoreCase("4")) {
            throw new AppException("监护人1与学生关系只能为数字1或2或3或4，1代表父亲，2代表母亲，3代表祖父母或外祖父母，4代表其它");
        }

        //校验监护人2与学生关系是否符合要求
        if (StringUtils.isNotBlank(vo.getSlaveGuardianRelation())
                && !vo.getSlaveGuardianRelation().equalsIgnoreCase("1")
                && !vo.getSlaveGuardianRelation().equalsIgnoreCase("2")
                && !vo.getSlaveGuardianRelation().equalsIgnoreCase("3")
                && !vo.getSlaveGuardianRelation().equalsIgnoreCase("4")) {
            throw new AppException("监护人2与学生关系只能为数字1或2或3或4，1代表父亲，2代表母亲，3代表祖父母或外祖父母，4代表其它");
        }

        //校验区县码是否为数字
        if (!StringUtils.isNumeric(vo.getApplyCountyCode())) {
            throw new AppException("报名所在区县码只能为数字");
        }
    }

    @Override
    @OpApi(funcCode = "syncJhApply0004", title = "金华市报名专库同步情况表新增", funcType = FuncTypeEnum.insert)
    @SaveOplog(
            operationType = "syncJhApply0004",
            operationName = "金华市报名专库同步情况表新增",
            dataType = "jhApplyId",
            auType = AuTypeEnum.OTHER)
    @Transactional
    public EduSyncJhApplyVO add(EduSyncJhApplyVO vo) {
        //保存前校验
        checkSave(vo);
        //校验年份和身份证是否存在
        QueryWrapper<EduSyncJhApply> queryWrapper = new QueryWrapper<>();
        queryWrapper.lambda().eq(EduSyncJhApply::getYear, vo.getYear())
                .eq(EduSyncJhApply::getApplyCountyCode, vo.getApplyCountyCode())
                .eq(EduSyncJhApply::getIdcard, vo.getIdcard());
        if (eduSyncJhApplyService.count(queryWrapper) > 0) {
            throw new AppException("该学生在报名区域已存在本年度数据!  ");
        }
        //保存数据
        EduSyncJhApply entity = Convert.convert(EduSyncJhApply.class, vo);
        eduSyncJhApplyService.save(entity);
        //将主键设置回vo使操作日志可以取到
        vo.setJhApplyId(entity.getJhApplyId());
        return vo;
    }

    @Override
    @OpApi(funcCode = "syncJhApply0005", title = "金华市报名专库同步情况表修改", funcType = FuncTypeEnum.update)
    @SaveOplog(
            operationType = "syncJhApply0005",
            operationName = "金华市报名专库同步情况表修改",
            dataType = "jhApplyId",
            auType = AuTypeEnum.OTHER)
    @Transactional
    public EduSyncJhApplyVO edit(EduSyncJhApplyVO vo) {
        if (ObjectUtils.isEmpty(vo.getJhApplyId())) {
            throw new AppException("传入需要修改的主键不能为空!  ");
        }
        EduSyncJhApply oldEntity = eduSyncJhApplyService.getById(vo.getJhApplyId());
        if (ObjectUtils.isEmpty(oldEntity)) {
            throw new AppException("查询不到需要修改的信息!  ");
        }
        //保存前校验
        checkSave(vo);
        //校验年份和身份证是否存在
        QueryWrapper<EduSyncJhApply> queryWrapper = new QueryWrapper<>();
        queryWrapper.lambda().eq(EduSyncJhApply::getYear, vo.getYear())
                .eq(EduSyncJhApply::getApplyCountyCode, vo.getApplyCountyCode())
                .eq(EduSyncJhApply::getIdcard, vo.getIdcard());
        if (eduSyncJhApplyService.count(queryWrapper) >= 1) {
            throw new AppException("该学生在报名区域已存在本年度数据!  ");
        }
        //保存数据
        EduSyncJhApply entity = Convert.convert(EduSyncJhApply.class, vo);
        eduSyncJhApplyService.updateById(entity);
        return vo;
    }


    @Override
    @OpApi(funcCode = "syncJhApply0006", title = "金华市报名专库同步情况表根据主键删除", funcType = FuncTypeEnum.delete)
    @SaveOplog(
            operationType = "syncJhApply0006",
            operationName = "金华市报名专库同步情况表删除",
            dataType = "jhApplyId",
            auType = AuTypeEnum.OTHER)
    @Transactional
    public void del(EduSyncJhApplyKeyVO vo) {
        if (ObjectUtils.isEmpty(vo.getJhApplyId())) {
            throw new AppException("传入需要删除的数据主键不能为空!  ");
        }
        EduSyncJhApply entity = eduSyncJhApplyService.getById(vo.getJhApplyId());
        if (ObjectUtils.isEmpty(entity)) {
            throw new AppException("未找到需要删除的信息!  ");
        }
        //判断录取库有没有学生数据，有的提示先删除录取库数据
        QueryWrapper<EduSyncJhEnroll> queryWrapper = new QueryWrapper<>();
        queryWrapper.lambda().eq(EduSyncJhEnroll::getIdcard, entity.getIdcard())
                .eq(EduSyncJhEnroll::getYear, entity.getYear());
        if (enrollService.count(queryWrapper) > 0) {
            throw new AppException("该学生在录取同步表库中有数据，无法删除报名同步库中的数据！");
        }
        //判断审核库是否有学生数据
        QueryWrapper<EduSyncJhApprove> approveWrapper = new QueryWrapper<>();
        approveWrapper.lambda().eq(EduSyncJhApprove::getIdcard, entity.getIdcard())
                .eq(EduSyncJhApprove::getYear, entity.getYear());
        if (approveService.count(approveWrapper) > 0) {
            throw new AppException("该学生在审核同步表库中有数据，无法删除报名同步库中的数据！");
        }
        eduSyncJhApplyService.removeById(vo.getJhApplyId());
    }

    @Override
    @OpApi(funcCode = "syncJhApply0007", title = "金华市报名专库同步情况-excel导出",
            funcType = FuncTypeEnum.other, checkPrivilege = BoolEnum.FALSE)
    public void exportJhApplyInfo(EduSyncJhApplyQueryVO vo, HttpServletResponse response) {
        if (ObjectUtils.isEmpty(vo.getModelCode())) {
            throw new AppException("请传入模板编码!  ");
        }
        ExcelVO excelVO = new ExcelVO();
        excelVO.setModelCode(vo.getModelCode());
        Map<String, List<SelectVO>> dictMap = new HashMap<>();
        //设置学段
        dictMap.put("schoolCategory", importBatchIntegration.getSchoolCategory());
        //设置民族下拉框信息
        dictMap.put("nation", importBatchIntegration.getNationDict());
        //设置身份证类型下拉框信息
        dictMap.put("cardType", importBatchIntegration.getCardTypeDict());
        //设置性别
        dictMap.put("sex", importBatchIntegration.getSexDict());
        //获取户口类型
        dictMap.put("householdType", importBatchIntegration.getHouseHoldTypeDict());
        //获取和子女关系字典列表
        List<SelectVO> relation = importBatchIntegration.getRelationDict();
        dictMap.put("masterGuardianRelation", relation);
        dictMap.put("slaveGuardianRelation", relation);
        excelVO.setDictMap(dictMap);
        ExcelModelVO excelModelVO = excelIntegration.getModel(excelVO);
        List<ExcelParamVO> paramVOList = excelModelVO.getParamVOList();
        paramVOList = paramVOList.stream().filter(item ->{
            return !"remarks".equals(item.getKey());
        }).collect(Collectors.toList());
        excelModelVO.setParamVOList(paramVOList);

        String destFilePath = this.tempPath + "/" + IdUtil.fastSimpleUUID() + "." + excelModelVO.getExcelType();
        int pageNum = 1;
        //如果数据为空，就下载模板给他
        if(queryCountByVO(vo) == 0){
            excelCommonHelper.downloadModel(excelModelVO,null,response);
            return;
        }
        setData(excelModelVO, null, destFilePath, vo, pageNum);
        String fileName = "金华市报名同步数据" + DateUtil.today();
        if (StringUtils.isBlank(fileName)) {
            fileName =
                    excelModelVO.getModelName()
                            + "_"
                            + DateUtil.format(DateUtil.date(), DatePattern.PURE_DATETIME_PATTERN)
                            + "."
                            + (StringUtils.isBlank(excelModelVO.getExcelType())
                            ? "xls"
                            : excelModelVO.getExcelType());
        }
        File file = FileUtil.file(destFilePath);
        BufferedInputStream in = null;
        try {
            in = FileUtil.getInputStream(file);
            ServletUtil.write(response, in, "application/octet-stream;charset=utf-8", fileName);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (in != null) {
                try {
                    in.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
            FileUtil.del(destFilePath);
        }
    }

    private Integer queryCountByVO(EduSyncJhApplyQueryVO vo) {
        String loginAreaCode = SessionUtil.getAreaCode();
        if (StringUtils.isBlank(loginAreaCode)) {
            throw new AppException("登录的账号未配置行政区划，请联系管理员进行处理！");
        }
        QueryWrapper<EduSyncJhApply> queryWrapper = new QueryWrapper<>();
        String areaCode = eduHelper.thisAreaCode();
        //如果登录账号的行政区划和系统配置的行政区划/金华市 不一致，就比对行政区划
        if (!loginAreaCode.equals(areaCode)) {
            //比对行政区划
            queryWrapper.lambda().eq(EduSyncJhApply::getApplyCountyCode, loginAreaCode);
        }
        queryWrapper.lambda()
                .eq(!ObjectUtils.isEmpty(vo.getYear()),EduSyncJhApply::getYear, vo.getYear())
                .eq(StringUtils.isNotBlank(vo.getIdcard()), EduSyncJhApply::getIdcard, vo.getIdcard())
                .like(StringUtils.isNotBlank(vo.getName()), EduSyncJhApply::getName, vo.getName())
                .eq(StringUtils.isNotBlank(vo.getCardType()), EduSyncJhApply::getCardType, vo.getCardType())
                .eq(StringUtils.isNotBlank(vo.getSchoolCategory()), EduSyncJhApply::getSchoolCategory, vo.getSchoolCategory())
                .eq(StringUtils.isNotBlank(vo.getApplyCountyCode()), EduSyncJhApply::getApplyCountyCode, vo.getApplyCountyCode())
                .eq(StringUtils.isNotBlank(vo.getSex()), EduSyncJhApply::getSex, vo.getSex());
        return Math.toIntExact(eduSyncJhApplyService.count(queryWrapper));
    }

    @Override
    @OpApi(funcCode = "syncJhApply0008", title = "金华市报名专库同步情况-excel导入并保存",
            funcType = FuncTypeEnum.other, checkPrivilege = BoolEnum.FALSE)
    public void inputAndSaveJhApplyInfo(ExcelVO excelVO, MultipartFile file) {
        ExcelModelVO excelModelVO = excelUtils.getModel(excelVO);
        InputStream in = null;
        try {
            String areaCode = SessionUtil.getAreaCode();
            if (StringUtils.isBlank(areaCode)){
                throw new AppException("该账号未配置统筹区划，请先联系管理人员进行配置！");
            }
            // 根据行政区划来加锁
            String redisKey= EduCache.EdU_SYNC_JH_APPLY_LOCK+areaCode;
            boolean lock = false;
            lock = redisHelper.hasKey(redisKey);
            if (lock) {
                throw new AppException("报名专库同步正在同步中，请稍后再试！");
            }else{
                //获取不到，就设置锁
                redisHelper.set(redisKey, true, 7200);
            }
            try{
                in = file.getInputStream();
                //记录已经导入的证件信息
                List<String> idcardList = new ArrayList<>();
                List<Map<String, Object>> list = excelCommonHelper.readExcel(excelModelVO, in, true);
                if (CollectionUtils.isEmpty(list)) {
                    throw new AppException("导入的excel文档数据为空，请填写数据后重试!");
                }
                //插入成功得到批次id
                Long importBatchId = importBatchService.createImportBatch(JhSyncImportEnum.apply);
                for (Map<String, Object> map : list) {
                    EduSyncJhApplyVO syncJhApplyVO =
                            JSON.parseObject(JSON.toJSONString(map), EduSyncJhApplyVO.class);
                    //校验数据，如果有错误，拼接到remarks中
                    String remarks = "";
                    Boolean checkFlag = importCheck(syncJhApplyVO, remarks, idcardList);
                    if (!checkFlag) {
                        //插入到错误表中
                        EduSyncJhApplyError applyError = Convert.convert(EduSyncJhApplyError.class, syncJhApplyVO);
                        //设置操作人的行政区划
                        applyError.setAreaCode(SessionUtil.getAreaCode());
                        applyError.setImportBatchId(importBatchId);
                        applyErrorService.save(applyError);
                        continue;
                    }
                    //根据身份证校验数据是否存在
                    QueryWrapper<EduSyncJhApply> queryWrapper = new QueryWrapper<>();
                    queryWrapper.lambda()
                            .select(EduSyncJhApply::getJhApplyId)
                            .eq(EduSyncJhApply::getIdcard, syncJhApplyVO.getIdcard())
                            .eq(EduSyncJhApply::getYear, syncJhApplyVO.getYear());
                    //有数据查出来覆盖，没数据插入
                    EduSyncJhApply syncJhApply = eduSyncJhApplyService.getOne(queryWrapper, false);
                    if (!ObjectUtils.isEmpty(syncJhApply)) {
                        syncJhApplyVO.setJhApplyId(syncJhApply.getJhApplyId());
                    }
                    EduSyncJhApply apply = Convert.convert(EduSyncJhApply.class, syncJhApplyVO);
                    apply.setImportBatchId(importBatchId);
                    //单条插入
                    eduSyncJhApplyService.saveOrUpdate(apply);
                }
            }finally {
                redisHelper.del(redisKey);
            }
        } catch (AppException appEx) {
            throw appEx;
        } catch (IOException e) {
            throw new AppException("读取文件异常:" + e.getMessage());
        } finally {
            if (in != null) {
                try {
                    in.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
        }
    }

    /**
     * 导入校验
     *
     * @param syncJhApplyVO 导入的数据行
     * @param remarks       错误信息
     * @param idcardList
     * @return true：校验通过 false：校验不通过，有错误信息
     */
    private Boolean importCheck(EduSyncJhApplyVO syncJhApplyVO, String remarks, List<String> idcardList) {
        if (syncJhApplyVO.getYear() == null) {
            syncJhApplyVO.setYear(eduHelper.thisYear());
        }
        boolean flag = true;
        //校验证件类型
        if (StringUtils.isBlank(syncJhApplyVO.getCardType())) {
            remarks += "证件类型不能为空!  ";
            flag = false;
        } else {
            //证件类型不为空，去除空格
            String cardType = syncJhApplyVO.getCardType().trim();
            if (Validator.isChinese(cardType)) {
                //如果为中文 根据数据字典的value  得到key 值
                DictQueryVO dictQueryVO = new DictQueryVO();
                dictQueryVO.setDictField("CARDTYPE");
                dictQueryVO.setDictValue(cardType);
                String key = dictIntegration.getKey(dictQueryVO);
                if (StringUtils.isBlank(key)) {
                    remarks += "学生证件类型数据格式不对！ 正确格式： 如 身份证/护照/台胞证/台湾居民来往大陆通行证/港澳居民往来内地通行证/外国人永久居留身份证/其他证件!  ";
                    flag = false;
                } else {
                    syncJhApplyVO.setCardType(key);
                }
            }else{
                remarks += "学生证件类型数据格式不对！ 正确格式： 如 身份证/护照/台胞证/台湾居民来往大陆通行证/港澳居民往来内地通行证/外国人永久居留身份证/其他证件!  ";
                flag = false;
            }
            syncJhApplyVO.setIdcard(syncJhApplyVO.getIdcard().trim().toUpperCase());
            //证件类型为数字，判断是否为身份证类型
            if (CardTypeEnum.idcard.getType().equals(syncJhApplyVO.getCardType())
                    && !IdcardUtil.isValidCard(syncJhApplyVO.getIdcard())) {
                remarks += "学生证件数据格式不对！";
                flag = false;
            }

            //如果证件类型不是身份证类型
            if (!CardTypeEnum.idcard.getType().equals(syncJhApplyVO.getCardType())) {
                if (StringUtils.isBlank(syncJhApplyVO.getSex())) {
                    remarks += "选择其他证件类型时学生性别不能为空！";
                    flag = false;
                }
                String sex = syncJhApplyVO.getSex();
                //如果性别是中文
                if (Validator.isChinese(sex)) {
                    //如果为中文 根据数据字典的value  得到key 值
                    DictQueryVO dictQueryVO = new DictQueryVO();
                    dictQueryVO.setDictField("SEXZJ");
                    dictQueryVO.setDictValue(sex);
                    String key = dictIntegration.getKey(dictQueryVO);
                    if (StringUtils.isBlank(key)) {
                        remarks += "学生性别数据格式不对！正确格式：如 男/女!  ";
                        flag = false;
                    } else {
                        syncJhApplyVO.setSex(key);
                    }
                } else {
                    //如果为其他格式
                    remarks += "学生性别数据格式不对！正确格式：如 男/女!  ";
                    flag = false;
                }
            } else {
                //表示是身份证类型
                int gender = IdcardUtil.getGenderByIdCard(syncJhApplyVO.getIdcard());
                if (gender == 1) {
                    // 男
                    syncJhApplyVO.setSex("0");
                } else {
                    // 女
                    syncJhApplyVO.setSex("1");
                }
            }
        }

        //校验学段
        if (StringUtils.isBlank(syncJhApplyVO.getSchoolCategory())) {
            remarks += "学生学段不能为空!  ";
            flag = false;
        } else {
            String schoolCategory = syncJhApplyVO.getSchoolCategory().trim();
            if (Validator.isChinese(schoolCategory)) {
                DictQueryVO dictQueryVO = new DictQueryVO();
                dictQueryVO.setDictField("SCHOOLCATEGORY");
                dictQueryVO.setDictValue(schoolCategory);
                String key = dictIntegration.getKey(dictQueryVO);
                if (StringUtils.isBlank(key)) {
                    flag = false;
                    remarks += "学生学段数据格式不对！ 正确格式：如 小学/初中! ";
                } else {
                    syncJhApplyVO.setSchoolCategory(key);
                }
            } else {
                flag = false;
                remarks += "学生学段数据格式不对！ 正确格式：如 小学/初中! ";
            }
        }

        //校验民族，如果民族有填写
        if (StringUtils.isNotBlank(syncJhApplyVO.getNation())) {
            String nation = syncJhApplyVO.getNation().trim();
            if (Validator.isChinese(nation)) {
                //如果为中文 根据数据字典的value  得到key 值
                DictQueryVO dictQueryVO = new DictQueryVO();
                dictQueryVO.setDictField("NATION");
                dictQueryVO.setDictValue(nation);
                String key = dictIntegration.getKey(dictQueryVO);
                if (StringUtils.isBlank(key)) {
                    remarks += "学生民族数据格式不对！ 正确格式：如 汉族 或 蒙古族 ...等!  ";
                    flag = false;
                } else {
                    syncJhApplyVO.setNation(key);
                }
            } else {
                flag = false;
                remarks += "学生民族数据格式不对！ 正确格式：如 汉族 或 蒙古族 ...等!  ";
            }
        }

        //校验户口类型，如果户口类型有填写
        if (StringUtils.isNotBlank(syncJhApplyVO.getHouseholdType())) {
            String householdType = syncJhApplyVO.getHouseholdType().trim();
            if (Validator.isChinese(householdType)) {
                DictQueryVO dictQueryVO = new DictQueryVO();
                dictQueryVO.setDictField("JHHOUSEHOLDTYPE");
                dictQueryVO.setDictValue(householdType);
                String key = dictIntegration.getKey(dictQueryVO);
                if (StringUtils.isBlank(key)) {
                    flag = false;
                    remarks += "学生户口类型数据格式不对！ 正确格式：如 居民/农业/渔民/其他!  ";
                } else {
                    syncJhApplyVO.setHouseholdType(key);
                }
            } else {
                flag = false;
                remarks += "学生户口类型数据格式不对！ 正确格式：如 居民/农业/渔民/其他!  ";
            }
        }
        Boolean jhrExists = false;
        //监护人至少需要有一个
        if (StringUtils.isNotBlank(syncJhApplyVO.getMasterGuardianName())) {
            syncJhApplyVO.setMasterGuardianName(syncJhApplyVO.getMasterGuardianName().trim());
            if (StringUtils.isBlank(syncJhApplyVO.getMasterGuardianNumber())) {
                flag = false;
                remarks += "监护人1 证件号码必须填写!  ";
            } else {
                syncJhApplyVO.setMasterGuardianNumber(syncJhApplyVO.getMasterGuardianNumber().trim());
            }
            //监护人证件号和学生证件号不能一样
            if (syncJhApplyVO.getMasterGuardianNumber().equals(syncJhApplyVO.getIdcard())) {
                flag = false;
                remarks += "监护人1证件号码不能与学生证件号相同!  ";
            }
            if (StringUtils.isBlank(syncJhApplyVO.getMasterGuardianRelation())) {
                flag = false;
                remarks += "监护人1与学生关系不能为空!  ";
            } else {
                if (Validator.isChinese(syncJhApplyVO.getMasterGuardianRelation())) {
                    DictQueryVO dictQueryVO = new DictQueryVO();
                    dictQueryVO.setDictField("GUARDIANRELATION");
                    dictQueryVO.setDictValue(syncJhApplyVO.getMasterGuardianRelation());
                    String key = dictIntegration.getKey(dictQueryVO);
                    if (StringUtils.isBlank(key)) {
                        flag = false;
                        remarks += "监护人1与学生关系数据格式不对！ 正确格式：如 父亲/母亲/祖父母或外祖父母/其他!  ";
                    } else {
                        syncJhApplyVO.setMasterGuardianRelation(key);
                    }
                } else {
                    flag = false;
                    remarks += "监护人1与学生关系数据格式不对！ 正确格式：如 父亲/母亲/祖父母或外祖父母/其他";
                }
            }
            jhrExists = true;
        }
        if (StringUtils.isNotBlank(syncJhApplyVO.getSlaveGuardianName())) {
            syncJhApplyVO.setSlaveGuardianName(syncJhApplyVO.getSlaveGuardianName().trim());
            if (StringUtils.isBlank(syncJhApplyVO.getSlaveGuardianNumber())) {
                flag = false;
                remarks += "监护人2 证件号码必须填写!  ";
            } else {
                syncJhApplyVO.setSlaveGuardianNumber(syncJhApplyVO.getSlaveGuardianNumber().trim());
            }
            if (syncJhApplyVO.getSlaveGuardianNumber().equals(syncJhApplyVO.getIdcard())) {
                flag = false;
                remarks += "监护人2证件号码不能与学生证件号相同!  ";
            }
            if (StringUtils.isBlank(syncJhApplyVO.getSlaveGuardianRelation())) {
                flag = false;
                remarks += "监护人1与学生关系不能为空!  ";
            } else {
                if (Validator.isChinese(syncJhApplyVO.getSlaveGuardianRelation())) {
                    DictQueryVO dictQueryVO = new DictQueryVO();
                    dictQueryVO.setDictField("GUARDIANRELATION");
                    dictQueryVO.setDictValue(syncJhApplyVO.getSlaveGuardianRelation());
                    String key = dictIntegration.getKey(dictQueryVO);
                    if (StringUtils.isBlank(key)) {
                        remarks += "监护人1与学生关系数据格式不对！ 正确格式：如 父亲/母亲/祖父母或外祖父母/其他!  ";
                        flag = false;
                    } else {
                        syncJhApplyVO.setSlaveGuardianRelation(key);
                    }
                } else {
                    flag = false;
                    remarks += "监护人1与学生关系数据格式不对！ 正确格式：如 父亲/母亲/祖父母或外祖父母/其他!  ";
                }
            }
            jhrExists = true;
        }
        if (!jhrExists) {
            remarks += "至少需要填写一个监护人信息!  ";
            flag = false;
        }

        //校验报名所在区县名
        String areaCode = SessionUtil.getAreaCode();
        DictQueryVO dictQueryVO = new DictQueryVO();
        dictQueryVO.setDictField("AREA");
        dictQueryVO.setDictKey(areaCode);
        String areaName = dictIntegration.getValue(dictQueryVO);
        syncJhApplyVO.setApplyCountyName(areaName);
        syncJhApplyVO.setApplyCountyCode(areaCode);

        //校验时间格式
        if (StringUtils.isBlank(syncJhApplyVO.getApplyDate())) {
            flag = false;
            remarks += "报名时间不能为空!  ";
        } else {
            //校验是否为时间戳格式
            if (isValidDate(syncJhApplyVO.getApplyDate())) {
                //如果是时间，转换为时间戳
                SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
                Date date = null;
                try {
                    date = dateFormat.parse(syncJhApplyVO.getApplyDate());
                    long timestamp = date.getTime();
                    syncJhApplyVO.setApplyTime(timestamp);
                } catch (ParseException e) {
                    flag = false;
                    remarks += "报名时间格式不正确！ 正确格式如:2024-01-01 12:10:50    ";
                }
            }else if (isTimestampFormat(syncJhApplyVO.getApplyDate())){
                //如果是时间戳
                syncJhApplyVO.setApplyTime(Long.valueOf(syncJhApplyVO.getApplyDate()));
            }else{
                flag = false;
                remarks += "报名时间格式不正确！ 正确格式如:2024-01-01 12:10:50    ";
            }
        }

        //校验身份证是否存在
        if (idcardList.contains(syncJhApplyVO.getIdcard())) {
            flag = false;
            remarks += "该身份证已存在，请检查!  ";
        } else {
            idcardList.add(syncJhApplyVO.getIdcard());
        }
        syncJhApplyVO.setRemarks(remarks);
        return flag;
    }

    private boolean isValidDate(String applyDate) {
        //判断时间是否为yyyy-MM-dd HH:mm:ss 格式
        DateTimeFormatter dtf = DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss");
        try {
            LocalDateTime.parse(applyDate, dtf);
            return true;
        } catch (DateTimeParseException e) {
            return false;
        }
    }

    @Override
    @OpApi(funcCode = "syncJhApply0009", title = "报名同步模板下载（PC）",
            funcType = FuncTypeEnum.other, checkPrivilege = BoolEnum.FALSE)
    public void downloadModel(ExcelVO vo, HttpServletResponse response) {
        //填写下拉框数据
        Map<String, List<SelectVO>> dictMap = new HashMap<>();
        //设置学段
        dictMap.put("schoolCategory", importBatchIntegration.getSchoolCategory());
        //设置民族下拉框信息
        dictMap.put("nation", importBatchIntegration.getNationDict());
        //设置身份证类型下拉框信息
        dictMap.put("cardType", importBatchIntegration.getCardTypeDict());
        //设置性别
        dictMap.put("sex", importBatchIntegration.getSexDict());
        //获取户口类型
        dictMap.put("householdType", importBatchIntegration.getHouseHoldTypeDict());
        //获取和子女关系字典列表
        List<SelectVO> relation = importBatchIntegration.getRelationDict();
        dictMap.put("masterGuardianRelation", relation);
        dictMap.put("slaveGuardianRelation", relation);
        vo.setDictMap(dictMap);
        ExcelModelVO excelModelVO=excelIntegration.getModel(vo);
        List<ExcelParamVO> paramVOList = excelModelVO.getParamVOList();
        //模板下载去除错误描述和区县名
        List<String> removeParams = new ArrayList<>();
        removeParams.add("remarks");
        removeParams.add("applyCountyName");
        paramVOList = paramVOList.stream().filter(item ->{
            return !removeParams.contains(item.getKey());
        }).collect(Collectors.toList());
        excelModelVO.setParamVOList(paramVOList);
        excelCommonHelper.downloadModel(excelModelVO,vo.getRows(),response);
    }

    @Override
    @Transactional
    public void syncDelete(EduSyncJhDeleteQueryVO vo) {
        //判断录取库有没有学生数据，有的提示先删除录取库数据
        QueryWrapper<EduSyncJhEnroll> enrollWrapper = new QueryWrapper<>();
        enrollWrapper.lambda().eq(EduSyncJhEnroll::getIdcard, vo.getIdcard())
                .eq(EduSyncJhEnroll::getApplyCountyCode, vo.getApplyCountyCode())
                .eq(EduSyncJhEnroll::getYear, vo.getYear());
        if (enrollService.count(enrollWrapper) > 0) {
            throw new AppException("该学生在录取同步表库中有数据，无法删除报名同步库中的数据！");
        }
        //判断审核库是否有学生数据
        QueryWrapper<EduSyncJhApprove> approveWrapper = new QueryWrapper<>();
        approveWrapper.lambda().eq(EduSyncJhApprove::getIdcard, vo.getIdcard())
                .eq(EduSyncJhApprove::getApplyCountyCode, vo.getApplyCountyCode())
                .eq(EduSyncJhApprove::getYear, vo.getYear());
        if (approveService.count(approveWrapper) > 0) {
            throw new AppException("该学生在审核同步表库中有数据，无法删除报名同步库中的数据！");
        }
        QueryWrapper<EduSyncJhApply> queryWrapper = new QueryWrapper<>();
        queryWrapper.lambda()
                .eq(EduSyncJhApply::getYear, vo.getYear())
                .eq(EduSyncJhApply::getApplyCountyCode, vo.getApplyCountyCode())
                .eq(EduSyncJhApply::getIdcard, vo.getIdcard());
        EduSyncJhApply syncJhApply = eduSyncJhApplyService.getOne(queryWrapper, false);
        if (ObjectUtils.isEmpty(syncJhApply)) {
           return;
        }
        eduSyncJhApplyService.removeById(syncJhApply.getJhApplyId());
    }

    /**
     * 提供给外部接口调用，保存同步学生报名信息
     * @param vo
     */
    @Override
    @Transactional
    @OpApi(funcCode = "syncSaveJhApply0001", title = "金华市学生报名信息同步-向外暴露接口",
            funcType = FuncTypeEnum.insert, checkPrivilege = BoolEnum.FALSE,savelog = true)
    public EduSyncJhApplyVO saveSyncApplyInfo(EduSyncJhApplyVO vo, HttpServletRequest request) {
        String appId = request.getHeader("appId");
        if (StringUtils.isBlank(appId)){
            throw new AppException("应用id不能为空！");
        }
        //保存前校验
        checkSave(vo);
        DictQueryVO areaDictQueryVO = new DictQueryVO();
        areaDictQueryVO.setDictField("AREA");
        areaDictQueryVO.setDictKey(vo.getApplyCountyCode());
        String areaName = dictIntegration.getValue(areaDictQueryVO);
        if (StringUtils.isBlank(areaName)){
            throw new AppException("传入的区县编码不正确，请根据提供的接口文档规范上传！");
        }
        vo.setApplyCountyName(areaName);

        //校验行政区划和当前配置的应用id 是否一样
        SysApplication sysApplication = applicationService.getById(appId);
        //根据，来分割remarks
        String remarks = sysApplication.getRemarks();
        if (StringUtils.isEmpty(remarks)){
            throw new AppException("应用id未配置行政区划，请联系管理员进行配置！");
        }
        List areaList = Arrays.asList(remarks.split(","));
        if (!areaList.contains(vo.getApplyCountyCode())){
            throw new AppException("传入的区县编码和申请的应用区县编码不一致，应用区县编码 ["+remarks+"]请根据提供的接口文档规范上传！");
        }

        if (StringUtils.isNotBlank(vo.getNation())){
            DictQueryVO nationDictQueryVO =  new DictQueryVO();
            nationDictQueryVO.setDictField("NATION");
            nationDictQueryVO.setDictKey(vo.getNation());
            String nationName = dictIntegration.getValue(nationDictQueryVO);
            if (StringUtils.isBlank(nationName)){
                throw new AppException("传入的民族编码不正确，请根据提供的接口文档规范上传！");
            }
        }

        //保存数据
        EduSyncJhApply entity = Convert.convert(EduSyncJhApply.class, vo);

        //校验报名数据一个区是唯一的
        QueryWrapper<EduSyncJhApply> queryWrapper = new QueryWrapper<>();
        queryWrapper.lambda()
                .eq(EduSyncJhApply::getYear, vo.getYear())
                .eq(EduSyncJhApply::getApplyCountyCode, entity.getApplyCountyCode())
                .eq(EduSyncJhApply::getIdcard, vo.getIdcard());
        if (eduSyncJhApplyService.count(queryWrapper) > 0) {
            //如果存在，取出学生信息，判断行政区划是否与应用配置的行政区划一致。
            EduSyncJhApply syncJhApply = eduSyncJhApplyService.getOne(queryWrapper, false);
            //设置主键
            entity.setJhApplyId(syncJhApply.getJhApplyId());
        }
        eduSyncJhApplyService.saveOrUpdate(entity);
        //将主键设置回vo使操作日志可以取到
        vo.setJhApplyId(entity.getJhApplyId());
        return vo;
    }

    @Override
    @Transactional
    public EduSyncJhApplyVO saveSyncApplyInfo2(EduSyncJhApplyVO vo) {
        //保存前校验
        checkSave(vo);
        DictQueryVO areaDictQueryVO = new DictQueryVO();
        areaDictQueryVO.setDictField("AREA");
        areaDictQueryVO.setDictKey(vo.getApplyCountyCode());
        String areaName = dictIntegration.getValue(areaDictQueryVO);
        if (StringUtils.isBlank(areaName)){
            throw new AppException("传入的区县编码不正确，请根据提供的接口文档规范上传！");
        }
        vo.setApplyCountyName(areaName);
        //保存数据
        EduSyncJhApply entity = Convert.convert(EduSyncJhApply.class, vo);

        //根据年份，身份证，判断是否存在
        QueryWrapper<EduSyncJhApply> queryWrapper = new QueryWrapper<>();
        queryWrapper.lambda()
                .eq(EduSyncJhApply::getYear, vo.getYear())
                .eq(EduSyncJhApply::getApplyCountyCode, entity.getApplyCountyCode())
                .eq(EduSyncJhApply::getIdcard, vo.getIdcard());
        EduSyncJhApply syncJhApply = eduSyncJhApplyService.getOne(queryWrapper, false);
        if (!ObjectUtils.isEmpty(syncJhApply)){
            //如果有数据，就覆盖
            entity.setJhApplyId(syncJhApply.getJhApplyId());
        }
        eduSyncJhApplyService.saveOrUpdate(entity);
        //将主键设置回vo使操作日志可以取到
        vo.setJhApplyId(entity.getJhApplyId());
        return vo;
    }

    private void setData(ExcelModelVO excelModelVO, ExcelWriter excelWriter, String destFilePath, EduSyncJhApplyQueryVO vo, int pageNum) {
        vo.setPageNum(pageNum);
        vo.setPageSize(500);
        boolean closeFlag = false;
        try {
            PageInfo<EduSyncJhApplyVO> pageInfo = getList(vo);
            String total = String.valueOf(pageInfo.getTotal());
            List<Map<String, Object>> list = new ArrayList<>();
            if (!CollectionUtils.isEmpty(pageInfo.getList())) {
                for (EduSyncJhApplyVO syncJhApplyVO : pageInfo.getList()) {
                    //将数据字典的key 转换为value
                    dictKeyToValue(syncJhApplyVO);
                    list.add(BeanUtil.beanToMap(syncJhApplyVO, false, true));
                }
            }
            if (pageNum == 1) {
                if (excelWriter == null) {
                    excelWriter = excelCommonHelper.createExcelWriter(excelModelVO, destFilePath, Integer.parseInt(total));
                }
                excelCommonHelper.writeMap(excelWriter, list);
            } else {
                excelCommonHelper.writeMap(excelWriter, list);
            }
            if (pageInfo.isHasNextPage()) {
                pageNum = pageNum + 1;
                setData(excelModelVO, excelWriter, destFilePath, vo, pageNum);
            } else {
                closeFlag = true;
            }
        } catch (AppException appEx) {
            throw appEx;
        } catch (Exception e) {
            throw new AppException("调用自定义写入服务异常:" + e.getMessage());
        } finally {
            if (closeFlag && excelWriter != null) {
                excelWriter.close();
            }
        }
    }

    /**
     * 将数据字典的key 转 value
     *
     * @param syncJhApplyVO
     */
    private void dictKeyToValue(EduSyncJhApplyVO syncJhApplyVO) {
        //转换学段
        if (StringUtils.isNotBlank(syncJhApplyVO.getSchoolCategory())) {
            DictQueryVO dictQueryVO = new DictQueryVO();
            dictQueryVO.setDictField("SCHOOLCATEGORY");
            dictQueryVO.setDictKey(syncJhApplyVO.getSchoolCategory());
            String schoolCategory = dictIntegration.getValue(dictQueryVO);
            syncJhApplyVO.setSchoolCategory(schoolCategory);
        }

        //转换证件类型
        if (StringUtils.isNotBlank(syncJhApplyVO.getCardType())) {
            DictQueryVO dictQueryVO = new DictQueryVO();
            dictQueryVO.setDictField("CARDTYPE");
            dictQueryVO.setDictKey(syncJhApplyVO.getCardType());
            String cardType = dictIntegration.getValue(dictQueryVO);
            syncJhApplyVO.setCardType(cardType);
        }

        //转换性别
        if (StringUtils.isNotBlank(syncJhApplyVO.getSex())) {
            DictQueryVO dictQueryVO = new DictQueryVO();
            dictQueryVO.setDictField("SEXZJ");
            dictQueryVO.setDictKey(syncJhApplyVO.getSex());
            String sex = dictIntegration.getValue(dictQueryVO);
            syncJhApplyVO.setSex(sex);
        }

        //转换民族
        if (StringUtils.isNotBlank(syncJhApplyVO.getNation())) {
            DictQueryVO dictQueryVO = new DictQueryVO();
            dictQueryVO.setDictField("NATION");
            dictQueryVO.setDictKey(syncJhApplyVO.getNation());
            String nation = dictIntegration.getValue(dictQueryVO);
            syncJhApplyVO.setNation(nation);
        }

        //转换户口类型
        if (StringUtils.isNotBlank(syncJhApplyVO.getHouseholdType())) {
            DictQueryVO dictQueryVO = new DictQueryVO();
            dictQueryVO.setDictField("JHHOUSEHOLDTYPE");
            dictQueryVO.setDictKey(syncJhApplyVO.getHouseholdType());
            String householdType = dictIntegration.getValue(dictQueryVO);
            syncJhApplyVO.setHouseholdType(householdType);
        }

        //转换监护人1 与学生关系
        if (StringUtils.isNotBlank(syncJhApplyVO.getMasterGuardianRelation())) {
            DictQueryVO dictQueryVO = new DictQueryVO();
            dictQueryVO.setDictField("GUARDIANRELATION");
            dictQueryVO.setDictKey(syncJhApplyVO.getMasterGuardianRelation());
            String guardian1Relation = dictIntegration.getValue(dictQueryVO);
            syncJhApplyVO.setMasterGuardianRelation(guardian1Relation);
        }

        //转换监护人2 与学生关系
        if (StringUtils.isNotBlank(syncJhApplyVO.getSlaveGuardianRelation())) {
            DictQueryVO dictQueryVO = new DictQueryVO();
            dictQueryVO.setDictField("GUARDIANRELATION");
            dictQueryVO.setDictKey(syncJhApplyVO.getSlaveGuardianRelation());
            String guardian2Relation = dictIntegration.getValue(dictQueryVO);
            syncJhApplyVO.setSlaveGuardianRelation(guardian2Relation);
        }
        //时间戳转换为日期格式
        if (syncJhApplyVO.getApplyTime() != null) {
            Date date = new Date(syncJhApplyVO.getApplyTime());
            SimpleDateFormat sdf  = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
            // 将Date对象格式化为字符串
            String applyDate = sdf.format(date);
            syncJhApplyVO.setApplyDate(applyDate);
        }
    }

    // 判断字符串是否为时间戳格式
    public  boolean isTimestampFormat(String timestampString) {
        if (timestampString == null || timestampString.isEmpty()) {
            return false;
        }
        try {
            long timestamp = Long.parseLong(timestampString);
            // 使用 SimpleDateFormat 来验证是否符合日期格式
            SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
            // 设置dateFormat为严格解析模式，禁止不严格的解析
            dateFormat.setLenient(true);
            dateFormat.format(timestamp);
            int currentYear = LocalDateTime.now().getYear();
            int nowYear = LocalDateTime.now().getYear();
            int yearDiff = Math.abs(currentYear - nowYear);
            return yearDiff <= 3;
        } catch (Exception e) {
            // 解析失败，则说明不是时间戳格式
            return false;
        }
    }
}
